﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class IDTypeDAL
    {
        private const string SQL_SELECT_WS_ID_TYPE = "select IDT_TYPE_ID as TYPE_ID,IDT_TYPE_NAME as TYPE_NAME,IDT_ENABLED as ENABLED,IDT_SORT_INDEX as SORT_INDEX,IDT_RCRE_STAFF_ID as RCRE_STAFF_ID,IDT_RCRE_TIME as RCRE_TIME,IDT_LCHG_STAFF_ID as LCHG_STAFF_ID,IDT_LCHG_TIME as LCHG_TIME from WS_ID_TYPE where 1=1";
        private const string SQL_INSERT_WS_ID_TYPE = "insert into WS_ID_TYPE(IDT_TYPE_ID,IDT_TYPE_NAME,IDT_ENABLED,IDT_SORT_INDEX,IDT_RCRE_STAFF_ID,IDT_RCRE_TIME) values(@TypeId,@TypeName,@Enabled,@SortIndex,@LastStaffId,@LastTime)";
        private const string SQL_UPDATE_WS_ID_TYPE = "update WS_ID_TYPE set IDT_TYPE_NAME=@TypeName,IDT_ENABLED=@Enabled,IDT_SORT_INDEX=@SortIndex,IDT_LCHG_STAFF_ID=@LastStaffId,IDT_LCHG_TIME=@LastTime where 1=1";
        private const string SQL_DELETE_WS_ID_TYPE = "delete from WS_ID_TYPE where 1=1";
        private const string SQL_COUNT_WS_ID_TYPE = "select count(IDT_TYPE_ID) from WS_ID_TYPE where 1=1";

        private const string SQL_WHERE_TYPE_ID = " and IDT_TYPE_ID=@TypeId ";
        private const string SQL_WHERE_TYPE_ID_NAME = " and IDT_TYPE_ID<>@TypeId and IDT_TYPE_NAME=@TypeName ";
        private const string SQL_WHERE_ENABLED = " and IDT_ENABLED=@Enabled ";

        private const string SQL_ORDER_BY_SORT_INDEX = " order by SORT_INDEX ";

        private const string PARM_TYPE_ID = "@TypeId";
        private const string PARM_TYPE_NAME = "@TypeName";
        private const string PARM_ENABLED = "@Enabled";
        private const string PARM_SORT_INDEX = "@SortIndex";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";

        Nullable<DateTime> NullDateTime = null;
        private static IDTypeDAL instance = new IDTypeDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static IDTypeDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet()
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_ID_TYPE);
            sbSQL.Append(SQL_ORDER_BY_SORT_INDEX);

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), null);
        }
        public DataSet GetDataSet(bool enabled)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_ID_TYPE);
            sbSQL.Append(SQL_WHERE_ENABLED);
            sbSQL.Append(SQL_ORDER_BY_SORT_INDEX);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ENABLED, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = enabled ? "Y" : "N" });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public List<IDTypeModel> Gets()
        {
            using (DataSet ds = GetDataSet())
            {
                DataTable dt = ds.Tables[0];
                List<IDTypeModel> list = dt.AsEnumerable().Select(t => new IDTypeModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    Enabled = string.Equals(Convert.ToString(t.ItemArray[2]), "Y"),
                    SortIndex = Convert.ToInt32(t.ItemArray[3]),
                    CreatedStaffId = Convert.ToString(t.ItemArray[4]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[5]),
                    LastModifiedStaffId = t.IsNull(6) ? string.Empty : Convert.ToString(t.ItemArray[6]),
                    LastModifiedDate = t.IsNull(7) ? NullDateTime : Convert.ToDateTime(t.ItemArray[7])
                }).ToList<IDTypeModel>();

                return list;
            }
        }
        public List<IDTypeModel> Gets(bool enabled)
        {
            using (DataSet ds = GetDataSet(enabled))
            {
                DataTable dt = ds.Tables[0];
                List<IDTypeModel> list = dt.AsEnumerable().Select(t => new IDTypeModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    Enabled = string.Equals(Convert.ToString(t.ItemArray[2]), "Y"),
                    SortIndex = Convert.ToInt32(t.ItemArray[3]),
                    CreatedStaffId = Convert.ToString(t.ItemArray[4]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[5]),
                    LastModifiedStaffId = t.IsNull(6) ? string.Empty : Convert.ToString(t.ItemArray[6]),
                    LastModifiedDate = t.IsNull(7) ? NullDateTime : Convert.ToDateTime(t.ItemArray[7])
                }).ToList<IDTypeModel>();

                return list;
            }
        }
        public int Insert(DbTransaction trans, IDTypeModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_TYPE_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_ENABLED, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Enabled ? "Y" : "N" });
            parms.Add(new MySqlParameter(PARM_SORT_INDEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.SortIndex });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_ID_TYPE, parms.ToArray());
        }
        public int Update(DbTransaction trans, IDTypeModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_ID_TYPE);
            sbSQL.Append(SQL_WHERE_TYPE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_TYPE_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_ENABLED, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Enabled ? "Y" : "N" });
            parms.Add(new MySqlParameter(PARM_SORT_INDEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.SortIndex });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Delete(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_ID_TYPE);
            sbSQL.Append(SQL_WHERE_TYPE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_ID_TYPE);
            sbSQL.Append(SQL_WHERE_TYPE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id, string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_ID_TYPE);
            sbSQL.Append(SQL_WHERE_TYPE_ID_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_TYPE_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
